#include "dbfactory.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlField>
#include <QDebug>

DBFactory* DBFactory::m_dbf = new DBFactory;
DBFactory* DBFactory::getInstance()
{
    return m_dbf;
}
void DBFactory::showAll()
{
    // 查询数据库中所有表的名称
    QStringList tables = m_db.tables();
    foreach(QString table, tables)
        qDebug()<<table;
    QSqlQuery query("SELECT * FROM finaldata");
    QSqlRecord record = query.record();
    for (int i = 0; i < record.count(); ++i)
    {
        QSqlField field = record.field(i);
        qDebug() << field;
    }
    while(query.next())
    {
        //        qDebug() << query.value(0).toString();
        QSqlRecord record = query.record();

        for(int i = 0; i < record.count(); i++)
        {
            QVariant value = record.value(i);
//            qDebug() << value;
        }
    }
}
DBFactory::DBFactory()
{
    m_tableName = "bigdata";

}

bool DBFactory::connect(QString username, QString password)
{
    m_db = QSqlDatabase::addDatabase("QODBC");
    m_db.setHostName("127.0.0.1");
    m_db.setPort(3306);
    m_db.setDatabaseName("testDB");
    m_db.setUserName(username);
    m_db.setPassword(password);
    
    bool ok = m_db.open();
    return ok;
}

QStringList DBFactory::getAllField()
{
    QStringList ret;
    QSqlQuery query("SHOW COLUMNS FROM " + m_tableName);
    while (query.next())
    {
//        qDebug() << query.record().value(0).toString();
        ret << query.record().value(0).toString();
    }
//    qDebug() << ret;
    return ret;
}

void DBFactory::getContentByField(QString fieldName, QVector<QVariant>& result)
{
    QString sql = QString("select distinct %1 from %2 where %3 is not null and %4 != '' order by %5").arg(fieldName).arg(m_tableName).arg(fieldName).arg(fieldName).arg(fieldName);
//    QString sql = QString("select %1 from %2 where %3 is not null and %4 != '' order by %5").arg(fieldName).arg(tableName).arg(fieldName).arg(fieldName).arg(fieldName);
//    if (fieldName == "学号")
//        qDebug() << sql;
    QSqlQuery query(sql);
    while (query.next())
    {
        result.append(query.record().value(0));
    }
}

int DBFactory::getRowCount()
{
    QString sql = QString("select count(*) from %1").arg(m_tableName);
//    qDebug() << sql;
    QSqlQuery query;
    while (query.next())
    {
        return query.record().value(0).toInt();
    }
    return -1;
}

void DBFactory::getAllInfo(QVector<QSqlRecord> &result)
{
    QString sql = QString("select * from %1").arg(m_tableName);
    QSqlQuery query;

    while (query.next())
    {
        QSqlRecord record = query.record();
        result.append(record);
    }
}

void DBFactory::getAllInfo(QVector<QVector<QVariant>> &result)
{
    QString sql = QString("select * from %1").arg(m_tableName);
    QSqlQuery query;
    bool queryResult = query.exec(sql);
//    if (queryResult)
//    {
//        qDebug() << sql << "exec successed.";
//    }
//    else
//    {
//        qDebug() << sql << "exec failed.";
//    }
    while (query.next())
    {
        QSqlRecord record = query.record();
        QVector<QVariant> curRecordVec;
        for (int i = 0; i < record.count(); ++i)
        {
            curRecordVec.append(record.value(i));
        }
        result.append(curRecordVec);
    }
}

void DBFactory::getSpecialInfo(QVector<QSqlRecord> &result, const QString& field, const QVector<QString>& conditions)
{
    // 首先判断字段类型
    QString detectSql = QString("select %1 from %2 limit 1").arg(field).arg(m_tableName);
    QSqlQuery detectQuery(detectSql);
    // 填充字符，数字是空格，字符串是单引号
    QString fullChar = "'";
    while (detectQuery.next())
    {
//        qDebug() << detectQuery.record().value(0);
//        qDebug() << "cur type:" << detectQuery.record().value(0).type();
        if (detectQuery.record().value(0).type() != QVariant::String)
        {
            fullChar = "";
        }
    }
//    qDebug() << isStrFlag;
    QString sql = QString("select * from %1").arg(m_tableName);
    if (!conditions.isEmpty())
    {
        sql += QString(" where %1 = %2%3%4").arg(field).arg(fullChar).arg(conditions[0]).arg(fullChar);
        for (int i = 1; i < conditions.size(); ++i)
        {
            sql += QString(" or %1 = %2%3%4").arg(field).arg(fullChar).arg(conditions[i]).arg(fullChar);
        }
    }
//    qDebug() << sql;
    QSqlQuery query(sql);
    while (query.next())
    {
        QSqlRecord record = query.record();
        result.append(record);
    }
}

void DBFactory::getSpecialInfo(QVector<QSqlRecord> &result, const QVector<QString>& field, const QVector<QVector<QString>>& conditionList)
{
//    bool allEmpty = true;
//    for (int fieldIndex = 0; fieldIndex < field.size(); ++fieldIndex)
//    {
//        if (!conditionList[fieldIndex].isEmpty())
//        {
//            allEmpty = false;
//            break;
//        }
//    }
//    if (allEmpty)
//    {
//        // 全部内容
//        return;
//    }
    QString sql = QString("select * from %1").arg(m_tableName);
    // 这里要先存到一个列表中，因为不知道那些字段条件非空
    QVector<QString> conditionSqlList;
    for (int fieldIndex = 0; fieldIndex < field.size(); ++fieldIndex)
    {

        QString cur = mergeCondition(field[fieldIndex], conditionList[fieldIndex]);
        if (!cur.isEmpty())
        {
            conditionSqlList.append(cur);
//            qDebug() << cur;
        }
    }
    if (!conditionSqlList.isEmpty())
    {
        sql += " where ";
        sql += conditionSqlList[0];
        for (int i = 1; i < conditionSqlList.size(); ++i)
        {
            sql += " and ";
            sql += conditionSqlList[i];
        }
    }
    QSqlQuery query(sql);
    while (query.next())
    {
        QSqlRecord record = query.record();
        result.append(record);
    }
//    qDebug() << sql;
}

// 获取数据表名称
void DBFactory::getTableNames(QVector<QString>& tableNameList)
{
    for (QString item: m_db.tables())
    {
//        qDebug() << item;
        tableNameList.append(item);
    }
}

// 设置当前数据表
void DBFactory::setCurrentTable(QString tableName)
{
    m_tableName = tableName;
}

// 合成条件字符串
// 第一个参数是字段名，第二个参数是筛选条件
QString DBFactory::mergeCondition(const QString& field, const QVector<QString>& conditions)
{
    // 没有条件返回空串
    if (conditions.isEmpty())
    {
        return "";
    }
    // 首先判断字段类型
    QString detectSql = QString("select %1 from %2 limit 1").arg(field).arg(m_tableName);
    QSqlQuery detectQuery(detectSql);
//    bool isStrFlag = true;
    // 填充字符，数字是空格，字符串是单引号
    QString fullChar = "'";
    while (detectQuery.next())
    {
//        qDebug() << detectQuery.record().value(0);
//        qDebug() << "cur type:" << detectQuery.record().value(0).type();
        if (detectQuery.record().value(0).type() != QVariant::String)
        {
//            isStrFlag = false;
            fullChar = "";
        }
    }
    QString result;
    result += QString(" (%1 = %2%3%4").arg(field).arg(fullChar).arg(conditions[0]).arg(fullChar);
    for (int i = 1; i < conditions.size(); ++i)
    {
        result += QString(" or %1 = %2%3%4").arg(field).arg(fullChar).arg(conditions[i]).arg(fullChar);
    }
    result += ") ";
    return result;
}
